﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using RESTServices.Dominio;
using System.Text;

namespace RESTServices.Persistencia
{
    public class PersonalDAO
    {
        public Personal Create(Personal oPersonal)
        {
            Personal personalCreado = null;
            try
            {
                SqlCommand sqlCommand = new SqlCommand("SP_REGISTRAR_PERSONAL");
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.Parameters.Add(new SqlParameter("@NOMBRESEMPLEADO", oPersonal.NOMBRESEMPLEADO));
                sqlCommand.Parameters.Add(new SqlParameter("@APEPATERNOEMPLEADO", oPersonal.APEPATERNOEMPLEADO));
                sqlCommand.Parameters.Add(new SqlParameter("@APEMATERNOEMPLEADO", oPersonal.APEMATERNOEMPLEADO));
                sqlCommand.Parameters.Add(new SqlParameter("@FECHANACIMIENTO", oPersonal.FECHANACIMIENTO));
                sqlCommand.Parameters.Add(new SqlParameter("@DIRECCIONEMPLEADO", oPersonal.DIRECCIONEMPLEADO));
                sqlCommand.Parameters.Add(new SqlParameter("@IDESTADOCIVIL", oPersonal.IDESTADOCIVIL));
                sqlCommand.Parameters.Add(new SqlParameter("@FLAGSEXO", oPersonal.FLAGSEXO));
                sqlCommand.Parameters.Add(new SqlParameter("@IDDEPDOMICILIO", oPersonal.IDDEPDOMICILIO));
                sqlCommand.Parameters.Add(new SqlParameter("@IDPROVDOMICILIO", oPersonal.IDPROVDOMICILIO));
                sqlCommand.Parameters.Add(new SqlParameter("@IDDISTDOMICILIO", oPersonal.IDDISTDOMICILIO));
                sqlCommand.Parameters.Add(new SqlParameter("@EMAIL", oPersonal.EMAIL));
                sqlCommand.Parameters.Add(new SqlParameter("@IDDOCIDENTIDAD", oPersonal.IDDOCIDENTIDAD));
                sqlCommand.Parameters.Add(new SqlParameter("@NUMERODOCUMENTO", oPersonal.NUMERODOCUMENTO));
                sqlCommand.Parameters.Add(new SqlParameter("@FLAGESTADO", oPersonal.FLAGESTADO));

                using (DataAccess adoDataService = new DataAccess())
                {
                    adoDataService.BeginTransaction();
                    Int32 orderID = adoDataService.ExecuteScalar(sqlCommand);
                    adoDataService.CommitTransaction();
                    adoDataService.Dispose();

                    personalCreado = Get(orderID);
                }

            }
            catch (Exception ex)
            {

            }

            return personalCreado;
        }

        public Personal Get(int personalID)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.Append(" SELECT * FROM PERSONAL  ");
            sqlBuilder.Append(" WHERE IDPersonal = @IDPersonal ");

            string sql = sqlBuilder.ToString();

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Parameters.Add("@IDPERSONAL", SqlDbType.Int);
            sqlCommand.Parameters["@IDPERSONAL"].Value = personalID;

            sqlCommand.CommandText = sql;

            using (DataAccess adoDataService = new DataAccess())
            {
                Personal oPersonal = new Personal();
                IDataReader dr = adoDataService.ExecuteReader(sqlCommand);

                if (dr.Read())
                {
                    oPersonal.IDPERSONAL = Convert.ToInt32(dr["IDPERSONAL"]);
                    oPersonal.NOMBRESEMPLEADO = Convert.ToString(dr["NOMBRESEMPLEADO"]);
                    oPersonal.APEPATERNOEMPLEADO = Convert.ToString(dr["APEPATERNOEMPLEADO"]);
                    oPersonal.APEMATERNOEMPLEADO = Convert.ToString(dr["APEMATERNOEMPLEADO"]);
                    oPersonal.NUMERODOCUMENTO = Convert.ToString(dr["NUMERODOCUMENTO"]);
                }
                dr.Close();

                adoDataService.Dispose();

                return oPersonal;
            }

        }

        public bool Delete(int personalID)
        {
            StringBuilder sqlBuilder = new StringBuilder();

            sqlBuilder.Append(" DELETE FROM PERSONAL  ");
            sqlBuilder.Append(" WHERE IDPERSONAL = @IDPERSONAL ");

            string sql = sqlBuilder.ToString();

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Parameters.Add("@IDPERSONAL", SqlDbType.Int);
            sqlCommand.Parameters["@IDPERSONAL"].Value = personalID;

            sqlCommand.CommandText = sql;

            using (DataAccess adoDataService = new DataAccess())
            {
                adoDataService.BeginTransaction();
                adoDataService.ExecuteNonQuery(sqlCommand);
                adoDataService.CommitTransaction();
                adoDataService.Dispose();

            }
            return true;
        }

        public List<Personal> List()
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append(" SELECT * FROM PERSONAL ");
            string sql = sqlBuilder.ToString();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = sql;

            using (DataAccess adoDataService = new DataAccess())
            {
                List<Personal> oPersonalList = new List<Personal>();
                IDataReader dr = adoDataService.ExecuteReader(sqlCommand);

                while (dr.Read())
                {
                    Personal oPersonal = new Personal();
                    oPersonal.IDPERSONAL = Convert.ToInt32(dr["IDPERSONAL"]);
                    oPersonal.NOMBRESEMPLEADO = Convert.ToString(dr["NOMBRESEMPLEADO"]);
                    oPersonal.APEPATERNOEMPLEADO = Convert.ToString(dr["APEPATERNOEMPLEADO"]);
                    oPersonal.APEMATERNOEMPLEADO = Convert.ToString(dr["APEMATERNOEMPLEADO"]);
                    oPersonal.NUMERODOCUMENTO = Convert.ToString(dr["NUMERODOCUMENTO"]);

                    oPersonalList.Add(oPersonal);
                }
                dr.Close();

                adoDataService.Dispose();

                return oPersonalList;
            }

        }

    }
}